import pymysql
class Mysql_User(object):
    def __init__(self):
        try:
            self.db = pymysql.connect(host="localhost",user="root",password="123456",database="Bishe")
            self.cursor = self.db.cursor()
            print("连接成功")
        except:
            print("连接失败")
    def login(self):
        sql = "select * from user"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results
    def legist(self,username,password,apiKey,userStatus,accessId,email,phone):
        sql = "INSERT INTO user (`username`, `password`, `API-KEY`,`user_status`,`access-id`,`Email`,`phone`) VALUES ('%s','%s','%s','%s','%s','%s','%s')" % (
        username, password, apiKey,userStatus,accessId,email,phone)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def getEmail(self,user):
        sql = "select * from user where username = '%s'"%(user)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results
    def changePwd(self,pwd,user):
        sql = "update user set password='%s' where username='%s' " % (pwd,user)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
        return "ok"
    def changeInfo(self,user,pwd,email,phone):
        sql = "update user set password='%s',Email='%s',phone='%s' where username='%s' " % (pwd, email,phone,user)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
        return "ok"
    def selectAll(self):
        sql = "select * from user order by user_id"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results
    def changeOneInfo(self,id,name,pwd,api,status,accessId,ema,pho):
        sql = "update user set username='%s',password='%s',API_Key='%s',user_status='%s',access_id='%s',Email='%s',phone='%s' where user_id='%s' " % (name, pwd, api, status,accessId,ema,pho,id)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
        return "ok"
    def deleteById(self,id):
        sql = "delete from user where user_id=" + str(id)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def selectByname(self,name):
        sql = "select * from user where username = '%s'" % (name)
        print(sql)
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            return results
        except:
            return "error"
    def addNew(self,name,pwd,apiKey,status,accessId,email,phone):
        sql = "INSERT INTO user (`username`, `password`, `API_Key`, `user_status`, `access-id`, `Email`, `phone`) VALUES ('%s','%s','%s','%s','%s','%s','%s')" % (
        name,pwd,apiKey,status,accessId,email,phone)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def __del__(self):
        self.db.close()